# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql

from conch_findroom.items import SellRoomDataItem,DealRoomDataItem,RentDataItem,NewDataItem,BorderItem

'''存入MySQL数据库'''
class MySQLPipeline(object):
    def __init__(self):
        self.connect = pymysql.connect(
        host = '127.0.0.1',      # ip
        port = 3306,                 # 端口
        user = 'root',               # 用户名
        password = 'cjs122374',      # 密码
        database = 'flask_spider',         # 连接数据库
        charset = 'utf8'             # 编码格式
        )
        self.cursor = self.connect.cursor()     # 执行命令游标

    def open_spider(self, spider):
        print('开启贝壳找房项目爬虫'.center(100,'*'))

    def process_item(self, item, spider):
        try:
            if isinstance(item,SellRoomDataItem):   # 在售二手房
                self.cursor.execute(
                    "insert into sell_eshouse values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    list(dict(item).values())
                )
            elif isinstance(item,DealRoomDataItem): # 成交二手房
                self.cursor.execute(
                    "insert into deal_eshouse values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    list(dict(item).values())
                )
            elif isinstance(item,RentDataItem): # 租房
                self.cursor.execute(
                    "insert into rent_house values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    list(dict(item).values())
                )
            elif isinstance(item, NewDataItem):  # 新房
                self.cursor.execute(
                    "insert into new_house values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    list(dict(item).values())
                )
            elif isinstance(item, BorderItem):  # 经纪人
                self.cursor.execute(
                    "insert into broker values (%s,%s,%s,%s,%s,%s,%s)",
                    list(dict(item).values())
                )

            # # self.connect.rollback()
            self.connect.commit()
        except Exception as e:
            print('插入错误：',e)
            self.connect.rollback()
        else:
            self.connect.commit() # mysql增删改查后需要 提交事务

        return item

    def close_spider(self,spider):
        self.cursor.close()
        self.connect.close()
        print('结束贝壳找房项目爬虫'.center(100, '*'))